﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ERPStudio;
using System.Data.SqlClient;
using System.IO;

namespace ResUma.Procese_Verbale
{
    public partial class OptiuniPV : FrmCard
    {
        private List<BindingSource> listBS = new List<BindingSource>();
        private List<SqlDataAdapter> listTA = new List<SqlDataAdapter>();

        public OptiuniPV(ModuleClass mc):base(mc)
        {
            InitializeComponent();
            workConnString = "ESRPConnectionString";
        }

        private void OptiuniPV_Load(object sender, EventArgs e)
        {
           SqlConnection con = DeschideConexiune(workConnString);
            this.resuma_CutiiCarneteTableAdapter.Connection = con;
            this.resuma_CutiiCarneteTableAdapter.Fill(this.eSRPDataSet.resuma_CutiiCarnete);
            InchideConexiune(con);
            AddButton("Genereaza", global::ERPStudio.Properties.Resources._001_03, DocumenteFrm_HandleTiparire, ToolStripItemAlignment.Right, true);
            AddButton("Export DBF", global::ERPStudio.Properties.Resources._001_04, ExportDBF, ToolStripItemAlignment.Right, true);

        }

        void ExportDBF(object sender, EventArgs e)
        {
            DataRowView dr = (DataRowView)resumaCutiiCarneteBindingSource.Current;
            if (dr == null || dr.IsNew)
                return;
            ESRPDataSet.resuma_CutiiCarneteRow ddr = dr.Row as ESRPDataSet.resuma_CutiiCarneteRow;

            saveFileDialog1.AddExtension = true;
            saveFileDialog1.FilterIndex = 1;
            saveFileDialog1.RestoreDirectory = true;
      
            saveFileDialog1.FileName = "CM241_" +ddr.NrCutieCM.ToString() +  "_3694594.dbf";
            saveFileDialog1.DefaultExt = ".dbf";
            saveFileDialog1.Filter = "FoxPro files (*.dbf)|*.dbf";
       
            if (this.saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                String nume_pre = String.Empty, cont_pers = String.Empty;
                String fName = String.Empty;
                System.Data.OleDb.OleDbConnection dBaseConnection = null;
                System.Data.OleDb.OleDbCommand dBaseCommand = null;
                FileInfo fi = new FileInfo(saveFileDialog1.FileName);
                String ConnectionString, path1 = fi.DirectoryName;
                fName = fi.Name.Replace(".DBF", "");
                ConnectionString = "Provider=VFPOLEDB.1;" + "Data Source = " + path1 + ";ANSI=True";
                //ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source = " + path1 + ";Extended Properties =dBase IV;";
                dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
                dBaseConnection.Open();
                if (fi.Exists)
                    fi.Delete();

                Int16 cateConturi = 0;

                SqlCommand sCmd = new SqlCommand("", DeschideConexiune(workConnString));
                sCmd.CommandText = "SELECT * FROM resuma_PVPredareCMScanat WHERE NrCutieCM=" + ddr.NrCutieCM.ToString();

                SqlDataReader sDR = sCmd.ExecuteReader();
                try
                {
                    dBaseCommand = new System.Data.OleDb.OleDbCommand("CREATE TABLE " + fName + " (cnp Numeric(12,0) NOT NULL, serie_cm CHAR(5) NOT NULL, nr_cm Numeric(7,0) NOT NULL, nume_pren CHAR(29) NOT NULL, nr_file Numeric(2,0) NOT NULL," +
                        "cui Numeric(12,0) NOT NULL, cod_prov CHAR(5) NOT NULL, nr_inreg Numeric(6,0) NOT NULL,nr_cutie Numeric(4,0) NOT NULL, facultate Numeric(0,0) NOT NULL, inchis_i Numeric(0,0) NOT NULL, inchis_a Numeric(0,0) NOT NULL, inchis_b Numeric(0,0), inchis_c Numeric(0,0) NOT NULL, operator_i CHAR(29) NOT NULL, cod_op_i Numeric(5,0) NOT NULL, data_i  DATE NOT NULL," +
                        "operator_a CHAR(29) NOT NULL, cod_op_a Numeric(5,0) NOT NULL,operator_b CHAR(29)  NOT NULL,cod_op_b Numeric(5,0)  NOT NULL,operator_c CHAR(29)  NOT NULL, cod_op_c Numeric(5,0)  NOT NULL, datad_a  DATE  NOT NULL,datai_a DATE  NOT NULL,  datad_b  DATE  NOT NULL,  datai_b DATE  NOT NULL, datad_c  DATE  NOT NULL, datai_c DATE  NOT NULL, tt_a CHAR(12)  NOT NULL, tt_b CHAR(12)  NOT NULL," +
                        "tt_c CHAR(12)  NOT NULL, tp_a CHAR(12)  NOT NULL, tp_b CHAR(12)  NOT NULL, nrl Numeric(3,0)  NOT NULL, nrl_ca Numeric(3,0)  NOT NULL, nrl_cb Numeric(3,0)  NOT NULL, etich Numeric(0,0)  NOT NULL,punctlucru CHAR(25)  NOT NULL, erra CHAR(1)  NOT NULL, errb CHAR(1)  NOT NULL, ctp Numeric(2,0)   NOT NULL,selectie Numeric(0,0) NOT NULL,cut_nou Numeric(4,0) NOT NULL)", dBaseConnection);
                    try
                    {
                        dBaseCommand.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        if (!ex.Message.Contains("already exists"))
                            MessageBox.Show(ex.Message);
                    }
                    String cnp=String.Empty,seriecm=String.Empty,nrcm=String.Empty,numep=String.Empty,filen=String.Empty;
                    while (sDR.Read())
                    {

                        cnp = sDR["cnp"].ToString().Trim();
                        seriecm = sDR["seriecm"].ToString().Trim();
                        nrcm = sDR["numarCM"].ToString();
                        
                        filen = sDR["NrFileCM"].ToString();
                        numep = sDR["NumePrenume"].ToString().Trim();
                        numep = numep.Substring(0, Math.Min(29,numep.Length));
                        Decimal nr_c = Decimal.Zero;
                        try
                        {
                            nr_c = Convert.ToDecimal(nrcm);

                            dBaseCommand.CommandText = "INSERT INTO " + fName + " (cnp, serie_cm, nr_cm,nume_pren,nr_file,cui,cod_prov,nr_inreg,nr_cutie," +
                            "facultate, inchis_i, inchis_a, inchis_b, inchis_c, operator_i, cod_op_i,data_i," +
                            "operator_a, cod_op_a,operator_b, cod_op_b,operator_c, cod_op_c, datad_a,datai_a,datad_b, datai_b,datad_c, datai_c,tt_a, tt_b," +
                            "tt_c, tp_a, tp_b, nrl, nrl_ca, nrl_cb, etich,punctlucru, erra, errb,ctp,selectie,cut_nou" +
                                ") VALUES (" + cnp + ",'" + seriecm + "'," + nrcm + ",'" + numep + "'," +
                                filen + ",3694594,'CUI',7977," + ddr.NrCutieCM.ToString() +
                                ",0,0,0,0,0,'',0,{}," + "'',0,'',0,'',0,{},{},{},{},{},{},'','','','','',0,0,0,0,'','','',0,0," + ddr.NrCutieCM.ToString() + ")";
                            dBaseCommand.ExecuteNonQuery();
                            cateConturi++;
                        }
                        catch(Exception ex)
                        {
                            MessageBox.Show(numep + ex.ToString());
                        }
                    }


                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                //case 8: //BRD
                dBaseConnection.Close();
 
                MessageBox.Show("Total exportat = " + cateConturi.ToString(),
                       "Info",
                       MessageBoxButtons.OK,
                       MessageBoxIcon.Information);
            }
        }

        void DocumenteFrm_HandleTiparire(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("", DeschideConexiune(workConnString));

            DataRowView dr = (DataRowView)resumaCutiiCarneteBindingSource.Current;
            if (dr == null || dr.IsNew)
                return;
            ESRPDataSet.resuma_CutiiCarneteRow ddr = dr.Row as ESRPDataSet.resuma_CutiiCarneteRow;

            ESRPDataSet eSRPDS = new ESRPDataSet();
            Cursor.Current = Cursors.AppStarting;
            String linia1 = String.Empty, linia2 = String.Empty;



            PreviewRaportFrm frm = new PreviewRaportFrm("", null, ModuleInfo);
            //eSRPDataSet - este data setul cu care lucram


            foreach (BindingSource bs in listBS)
                if (bs != null) bs.Dispose();
            listBS.Clear();
            listBS.Add(new BindingSource(eSRPDS, "resuma_PVPredareCMScanat"));

            listTA.Clear();
            listTA.Add(new SqlDataAdapter("SELECT * FROM resuma_PVPredareCMScanat WHERE NrCutieCM=" + ddr.NrCutieCM.ToString() + " ORDER BY PozCutieCM", cmd.Connection));
            listTA[0].SelectCommand.CommandType = CommandType.Text;
            listTA[0].Fill(eSRPDS.resuma_PVPredareCMScanat);

 
            InchideConexiune(cmd.Connection);


            //String artAl = artAlCB.Text.Substring(0, 2);
            //String diviziune = JurnalCB.Text + "." + GetTitlu(artAl) + "." + artAlCB.Text;


            frm.NumeRaport = "PV.rdlc";

            frm.AddDataSource("PV", listBS[0]);
   
            frm.SetWindowFormat(true);



            foreach (SqlDataAdapter da in listTA)
                if (da != null) da.Dispose();
            listTA.Clear();
            InchideConexiune(cmd.Connection);
            frm.MdiParent = this.MdiParent;

            //frm.AddParameter("Nume", nume, true);
            //frm.AddParameter("Functii", functii, true);

            frm.Show();
            cmd.Dispose();



            Cursor.Current = Cursors.Default;
        }
    }
}
